package com.jzwl.xydk.manager.importdata.dao;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.jzwl.common.id.Sequence;
import com.jzwl.system.base.dao.BaseDAO;
import com.jzwl.xydk.manager.importdata.pojo.ImportData;
import com.jzwl.xydk.wap.business.home.pojo.UserSkillclassF;

@Repository("importDataDao")
public class ImportDataDao {
	
	@Autowired
	private BaseDAO baseDAO;//dao基类，操作数据库
	
	public List<ImportData> getInfo() {
		String sql = "select t.id as id,t.city as city,t.provice as provice,t.school as school,t.`level` as level FROM `xiaoka-xydk`.`xk-city-school-info` t GROUP BY t.city";
		List<ImportData> data = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(ImportData.class));
		return data;
	}
	
	public List<ImportData> getCityInfo(String temp) {
		String sql = "select t.id as id from `xiaoka`.tg_city t where t.isDelete=0 and t.cityName LIKE '"+temp+"'";
		List<ImportData> data = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(ImportData.class));
		return data;
	}
	
	public List<ImportData> getSchoolInfo(String temp) {
		String sql = "select t.id as id from `xiaoka`.tg_school_info t where t.isDelete=0 and t.schoolName LIKE '"+temp+"'";
		List<ImportData> data = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(ImportData.class));
		return data;
	}
	
	public boolean addCity(String province,String cityName,Long id) {
		try {
			Map<String,Object> map = new HashMap<String, Object>();
			map.put("id", id);
			map.put("cityName", cityName);
			map.put("province", province);
			map.put("number", id.toString().substring(2, id.toString().length()));
			map.put("cityOperateManager", "325500290");
			map.put("phone", "13333333333");
			map.put("longitude", "0.0");
			map.put("latitude", "0.0");
			map.put("isDelete", "0");
			
			String sql = " insert into `xiaoka`.tg_city"
					+ "    (id,cityName,province,number,cityOperateManager,phone,longitude,latitude,isDelete) "
					+ "    values "
					+ "    (:id,:cityName,:province,:number,:cityOperateManager,:phone,:longitude,:latitude,:isDelete)";

			return baseDAO.executeNamedCommand(sql, map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean addSchool(String province,String cityName,Long id,String schoolName,Long cityId) {
		try {
			Map<String,Object> map = new HashMap<String, Object>();
			map.put("id", id);
			map.put("cityId", cityId);
			map.put("schoolName", schoolName);
			map.put("schoolNumber", id.toString().substring(3, id.toString().length()));
			map.put("schoolRespPeopleId", "292100590");
			map.put("phone", "13333333333");
			map.put("address",province+cityName );
			map.put("longitude", "0.0");
			map.put("latitude", "0.0");
			map.put("isDelete", "0");
			map.put("picUrl", "/upload/image/banner/20160522094515024021%E5%9B%9B%E5%B7%9D%E6%97%85%E6%B8%B8%E5%AD%A6%E9%99%A2.jpg");
			map.put("departmentId", "1456561647806002");
			
			String sql = " insert into `xiaoka`.tg_school_info"
					+ "    (id,cityId,schoolName,schoolNumber,schoolRespPeopleId,phone,address,longitude,latitude,isDelete,picUrl,departmentId) "
					+ "    values "
					+ "    (:id,:cityId,:schoolName,:schoolNumber,:schoolRespPeopleId,:phone,:address,:longitude,:latitude,:isDelete,:picUrl,:departmentId)";

			return baseDAO.executeNamedCommand(sql, map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	
	public boolean addSchoolLabel(Long dicDataId,Long schoolId) {
		try {
			Map<String,Object> map = new HashMap<String, Object>();
			map.put("id", Sequence.nextId());
			map.put("dicDataId", dicDataId);
			map.put("schoolId", schoolId);
			map.put("createTime", new Date());
			
			String sql = " insert into `xiaoka-xydk`.schoollabel"
					+ "    (id,dicDataId,schoolId,createTime) "
					+ "    values "
					+ "    (:id,:dicDataId,:schoolId,:createTime)";

			return baseDAO.executeNamedCommand(sql, map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public List<ImportData> getInfoByCity(String cityName) {
		String sql = "select t.id as id,t.city as city,t.provice as provice,t.school as school,t.`level` as level FROM `xiaoka-xydk`.`xk-city-school-info` t where t.city LIKE '"+cityName+"'";
		List<ImportData> data = baseDAO.getJdbcTemplate().query(sql, new Object[] {},
				ParameterizedBeanPropertyRowMapper.newInstance(ImportData.class));
		return data;
	}
	
	public Map<String, Object> getLabelData(String temp) {
		try {
			String sql = "select t.id as id,t.dic_name as dicName from `xiaoka`.v2_dic_data t where t.dic_name like '"+temp+"'";
		//	System.out.println(sql);
			return baseDAO.queryForMap(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	

}
